package AccesoADatos;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;


 public class ServicioTableSpace extends Servicio{
    
    private String OBTENERDATOS = "SELECT tablespace_name,total_bytes,used_bytes,free_bytes FROM (SELECT tablespace_name FROM dba_tablespaces) LEFT OUTER JOIN (SELECT tablespace_name, SUM(bytes) AS total_bytes FROM dba_data_files GROUP BY tablespace_name) USING (tablespace_name) LEFT OUTER JOIN (SELECT tablespace_name, sum(bytes) AS used_bytes from dba_segments GROUP BY tablespace_name) USING (tablespace_name) LEFT OUTER JOIN (SELECT tablespace_name, SUM(bytes) AS free_bytes FROM dba_free_space GROUP BY tablespace_name) USING (tablespace_name)";
    private static String GetTableSpace = "Select tablespace_name from dba_tablespaces";
    
    String user;
    String pass;
    String ip;
    String port;
    String db;
    
    public ServicioTableSpace(String user, String pass,String ip, String port, String db,ArrayList collection)
    {
        this.user = user;
        this.pass = pass;
        this.ip=ip;
        this.port=port;
        this.db=db;
    }
    
    public Collection listarTS() throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        Statement stmt = null;
        try{
            stmt = conexion.createStatement();          
            rs = stmt.executeQuery(GetTableSpace);
             while (rs.next()) {
                 String aux = rs.getString("tablespace_name");
                 if(aux.equals("TEMP")){
                // coleccion.add(aux);
                 }
                 else{
                     coleccion.add(aux);
                 }
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    public Collection listarTS(String nameLink) throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        Statement stmt = null;
        try{
            stmt = conexion.createStatement();
            String ej = GetTableSpace +"@"+nameLink;
            rs = stmt.executeQuery(ej);
             while (rs.next()) {
                 String aux = rs.getString("tablespace_name");
                 if(aux.equals("TEMP")){
                // coleccion.add(aux);
                 }
                 else{
                     coleccion.add(aux);
                 }
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    
}
